TRUNCATE TABLE ODS.EXCEL_DLAUNCH_LIST;
INSERT INTO ODS.EXCEL_DLAUNCH_LIST(ActivityName, ActivityID, ActivityType, ActivityStatus, ActivityTTLUnquiePges, DataSource, StartTime, ProductID, ProductName)
 VALUES
 ('test1', 'test8888', '1', '3', '15', 'ManualInput', '2020-06-15 15:14:51', null, 'Class1'),
 ('test2', 'test9167', '2', '3', '26', 'DBM', '2021-07-12 19:44:44', null, 'Class1'),
 ('test3', 'test9207', '1', '3', '8', 'ManualInput', '2020-07-22 22:17:38', null, 'Class1'),
 ('test4', 'test9207', '1', '3', '26', 'DBM', '2020-07-22 22:17:38', null, 'Class1'),
 ('test5', 'test8366', '2', '3', '20', 'DBM', '2021-02-04 18:03:19', null, 'Class2'),
 ('test6', 'test0691', '2', '3', '18', 'DBM', '2021-03-29 20:49:54', null, 'Class1'),
 ('test7', 'test3268', '2', '3', '23', 'DBM', '2021-04-28 21:47:21', null, 'Class2'),
 ('test8','test3013','2','3','11','DBM','2021-09-23 23:26:41',NULL,'Class1'),
 ('test9','test5059','2','3','35','DBM','2021-11-19 17:07:41',NULL,'Class3')
 ;

TRUNCATE TABLE  ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER;
INSERT INTO  ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER
SELECT DISTINCT ActivityID, DataType, DataSource, PageCode, 
CASE WHEN ( ActivityID = 'test8366'AND PageCode = 5 AND DataType = 'QA2')
		OR ( ActivityID = 'test0691' AND PageCode = 15)
THEN 0 ELSE 1 END TAG
from ChinaDW.STG.VW_T_DBM_DLAUNCH_RECORD;

ALTER TABLE STG.DBM_DLAUNCH_VISIT_RECORD ALTER COLUMN TimeSpand FLOAT;
ALTER TABLE ChinaDW.ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER ALTER COLUMN PageCode VARCHAR(50);

GO
-- 将 Access_record / answer_record / page_record / vedio_reocrd 合并为一张表
-- 便于后续统计页面数(问题回答数量，视频观看数量，页码阅读数量等 （现阶段仅对独立页面数量进行计数，）)
-- 2021221 修改Video表中时间字段，强转为Float型数据 video211221
CREATE VIEW STG.VW_T_DBM_DLAUNCH_RECORD as
SELECT 	Activity_Id ActivityID, Activity_Name ActivityName, User_Id UserId, User_Name UserName, 
		H5_Access_Id H5AccessId,  page_Code PageCode, DataType, 
		 Time_Spand TimeSpand, Login_Date LoginDate, 
		 cast(Create_Date as datetime) CreateDate ,
		 'DBM' DataSource -- 区分数据来源
FROM (
SELECT 	activity_id, activity_name, user_id, user_name, h5_access_id, page_code, DataType, time_spand,
		login_date, create_date
FROM (
		SELECT 	activity_id, activity_name , user_id , user_name , h5_access_id , 
				page_code, 
				'PAGE' DataType,5 time_spand, -- TODO 需要统一统计口径
				null login_date, create_time create_date
		FROM ods.DBM_ACT_DLAUNCH_PAGE_RECORD ddpr -- TODO 替换数据库和表名
		INNER join ods.EXCEL_DLAUNCH_LIST edl1 on edl1.ActivityID = ddpr.activity_id
	UNION all
		SELECT 
				activity_id, activity_name , user_id , user_name , h5_access_id ,
				page_code, DataType,  sum(time_spand) time_spand, -- 需要统一统计口径
				null login_date ,MIN(create_time) create_date
		FROM (
			SELECT 	activity_id, activity_name, user_id, user_name, h5_access_id, 
					question_code page_code, 
					case question_category when 1 then 'QA1' when 2 then 'QA2'
					else 'QA0' end as DataType, 0 time_spand, -- TODO 需要统一统计口径
					create_time
			FROM ods.DBM_ACT_DLAUNCH_ANSWER_RECORD dadar  -- TODO 替换数据库和表名
			INNER join ods.EXCEL_DLAUNCH_LIST edl2 on edl2.ActivityID = dadar.activity_id
		) QA1 
		group by activity_id, activity_name , user_id , user_name,
				h5_access_id , page_code, DataType
	UNION all
		SELECT 	activity_id, activity_name, user_id , user_name, h5_access_id, page_code, DataType, sum(time_spand) time_spand,
				min(login_datatime) login_datatime, min(create_datetime) create_datetime
		FROM (
			SELECT 	activity_id, activity_name, user_id , user_name, h5_access_id, 
					page_code ,'ACCESS' DataType, 5 time_spand,
					-- 数据源中存在24时的数据，将其替换为00
					cast(cast(login_data as date) as datetime) + 
					cast(cast(
					case when len(login_time) < 7 then login_time -- sql server中，时间类型会自动补0例如 2:8:7则会被转为 02:08:07
					else 
					case when left(login_time,2) = '24' then '00' + right(replace(login_time,' ',''),6) -- 时间标注为24时报错，替换为00 TODO 需要核实
					else login_time end
					end as time) as datetime) login_datatime ,
					create_time create_datetime
			FROM ods.DBM_ACT_DLAUNCH_ACCESS_RECORD adar 
			INNER join ods.EXCEL_DLAUNCH_LIST edl3 on edl3.ActivityID = adar.activity_id
			where year( login_data) > 2000 -- 数据源中存在 0011和 0010年的数据
		) acc1 group by activity_id, activity_name, user_id , user_name, h5_access_id, page_code, DataType
) record1
UNION all
SELECT 
	activity_id, activity_name, user_id, user_name, h5_access_id,
	video_code page_code, 'VIDEO' DataType,
	cast(view_length as FLOAT) timespand , --video211221
	null login_date, create_time 
FROM ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD dadvr -- TODO 替换数据库和表名
INNER join ods.EXCEL_DLAUNCH_LIST edl4 on edl4.ActivityID = dadvr.activity_id
) union_data
UNION ALL 
SELECT ActivityId, ActivityName, userId, UserName, H5AccessId, PageCode, DataType,
	TimeSpand, LoginDate, CreateDate,'ManualInput' DATASOURCE FROM ODS.EXCEL_DLAUNCH_RPM_MANUAL_INPUT -- TODO 看是否需要加
;